const express = require("express");
const cors = require("cors");
//加载fs模块--通过程序方式实时管理文件
const fs = require("fs");
//拖放文件上传
const multer = require("multer");
//命名随机
const uuid = require("uuid");
//bodyParser中间件
var bodyParser = require("body-parser");

//邮件模块
const mail = require("./public/mail");

const mysql = require("mysql");
//创建连接池
const pool = mysql.createPool({
  host: "127.0.0.1",
  port: 3306,
  user: "root",
  password: "",
  database: "CM",
  charset: "utf8",
  connectionLimit: 20,
});

const server = express();
server.use(
  bodyParser.urlencoded({
    extended: false,
  })
);
server.use(
  cors({
    origin: ["http://127.0.0.1:8080", "http://localhost:8080"],
  })
);

//创建总分类接口
server.get("/navlist", (req, res) => {
  let sql = "SELECT idname,pic FROM list";
  pool.query(sql, (err, result) => {
    if (err) throw err;
    res.send({
      result: result,
    });
  });
});

//明星大咖接口
server.get("/boss", (req, res) => {
  let sql = "SELECT id,actorName,actorImg,fans FROM actor";
  pool.query(sql, (err, result) => {
    if (err) throw err;
    res.send({
      result: result,
    });
  });
});

// 指定演员接口
server.get("/man", (req, res) => {
  let sql = "SELECT id,actorName,actorImg,fans FROM actor WHERE id=?";
  pool.query(sql, [req.query.id], (err, result) => {
    if (err) throw err;
    res.send({
      result: result,
    });
  });
});
//所有演出接口
server.get("/moreyc", (req, res) => {
  let sql =
    "SELECT id,verticalPic,name,price,address,date FROM allPerformances";
  pool.query(sql, (err, result) => {
    if (err) throw err;
    res.send({
      result: result,
    });
  });
});

// 指定演出接口
server.get("/tick", (req, res) => {
  let sql =
    "SELECT id,verticalPic,name,price,address,date,actor_id FROM allPerformances WHERE id=?";
  pool.query(sql, [req.query.id], (err, result) => {
    if (err) throw err;
    res.send({
      result: result,
    });
  });
});

// 推荐演出接口
server.get("/active", (req, res) => {
  let sql =
    "SELECT id,verticalPic,name,price,address,date FROM allPerformances LIMIT 1,4";
  pool.query(sql, (err, result) => {
    if (err) throw err;
    res.send({
      result: result,
    });
  });
});
//城市定位接口
server.get("/location", (req, res) => {
  let sql = "SELECT name,Initials FROM location";
  pool.query(sql, (err, result) => {
    if (err) throw err;
    res.send({
      result: result,
    });
  });
});

//发送注册邮件接口
server.post("/getemail", function(req, res) {
  let email = req.body.email;
  //随机生成6位验证码
  function createSixNum() {
    let Num = "";
    Num += Math.floor(Math.random() * 9) + 1;
    for (let i = 0; i < 5; i++) {
      Num += Math.floor(Math.random() * 10);
    }
    return Num;
  }
  let code = createSixNum();
  let msg='注册账号';
  let color='green'
  //查询是否已存在邮箱
  let sql = "SELECT id FROM user WHERE email=?";
  pool.query(sql, [email], (err, result) => {
    if (err) throw err;
    if (result.length == 0) {
      //未使用过此邮箱时
      mail.sendMails(email, code, msg,color,function(err) {
        if (err) {
          console.log("邮件发送失败");
          throw err;
        }
      });
      // 删除之前验证码
      let sql1 = "DELETE FROM codes WHERE email=?";
      pool.query(sql1, [email], (err) => {
        if (err) throw err;
        //插入新的验证码
        let sql2 = "INSERT INTO codes SET ?";
        pool.query(sql2, [{ email: email, number: code }], (err, result) => {
          if (err) throw err;
        });
        res.send({
          error: 1,
          msg: "验证码已经发送到您的邮箱，请注意查收",
        });
      });
    } else {
      //邮箱被使用
      res.send({
        error: 0,
        msg: "邮箱已被注册",
      });
    }
  });
});

// 发送修改密码邮件
server.post("/setpass", function(req, res) {
  let email = req.body.email;
  //随机生成4位验证码
  function createSixNum() {
    let Num = "";
    // 首位不为0
    Num += Math.floor(Math.random() * 9) + 1;
    for (let i = 0; i < 3; i++) {
      Num += Math.floor(Math.random() * 10);
    }
    return Num;
  }
  let code = createSixNum();
  let msg='修改密码';
  let color='red'
  //查询是否已存在邮箱
  let sql = "SELECT id FROM user WHERE email=?";
  pool.query(sql, [email], (err, result) => {
    if (err) throw err;
    if (result.length == 0) {
     res.send({
       code:0,
       msg:'邮箱不存在'
     })
    }else{
      mail.sendMails(email, code, msg,color,function(err) {
        if (err) {
          console.log("邮件发送失败");
          throw err;
        }
      });
      // 删除之前验证码
      let sql1 = "DELETE FROM codes WHERE email=?";
      pool.query(sql1, [email], (err) => {
        if (err) throw err;
        //插入新的验证码
        let sql2 = "INSERT INTO codes SET ?";
        pool.query(sql2, [{ email: email, number: code }], (err, result) => {
          if (err) throw err;
        });
        res.send({
          code: 1,
          msg: "验证码已经发送到您的邮箱，请注意查收",
        });
      });
    }
  });
});
// 用户修改密码
server.post("/setPassword", (req, res) => {
  //获取页面数据
  let username = req.body.username;
  let password = req.body.password;
  let email = req.body.email;
  let code = req.body.code;
  let sql = "SELECT id FROM codes WHERE email=? AND number=?";
  //验证验证码是否正确
  pool.query(sql, [email, code], (err, result) => {
    if (err) throw err;
    if (result.length == 0) {
      res.send({
        code: 1,
        msg: "验证码输入错误或已过期",
      });
    } else {
      // 验证是否存在用户
      let sql2 = "SELECT id FROM user WHERE username=? AND email=?";
      pool.query(sql2, [username,email], (err, result) => {
        if (err) throw err;
        if (result.length != 0) {
          let sql3 =
            "UPDATE user SET password=MD5(?) WHERE username=? AND email=?";
          //插入数据并删除验证码
          pool.query(sql3, [ password,username, email], (err, result) => {
            if (err) throw err;
            let sql4 = "DELETE FROM codes WHERE number=?";
            pool.query(sql4, [code], (err, result) => {
              if (err) throw err;
              res.send({
                code: 0,
                msg: "修改成功",
              });
            });
          });
        } else {
          res.send({
            code: 2,
            msg: "用户不存在",
          });
        }
      });
    }
  });
});

//用户注册
server.post("/zhuce", (req, res) => {
  //获取页面数据
  let username = req.body.username;
  let password = req.body.password;
  let email = req.body.email;
  let code = req.body.code;
  let sql = "SELECT id FROM codes WHERE email=? AND number=?";
  //验证验证码是否正确
  pool.query(sql, [email, code], (err, result) => {
    if (err) throw err;
    if (result.length == 0) {
      res.send({
        error: 1,
        msg: "验证码输入错误或已过期",
      });
    } else {
      // 验证是否存在用户
      let sql2 = "SELECT id FROM user WHERE username=?";
      pool.query(sql2, [username], (err, result) => {
        if (err) throw err;
        if (result.length == 0) {
          let sql3 =
            "INSERT INTO user(username,password,email) VALUES(?,MD5(?),?)";
          //插入数据并删除验证码
          pool.query(sql3, [username, password, email], (err, result) => {
            if (err) throw err;
            let sql4 = "DELETE FROM codes WHERE number=?";
            pool.query(sql4, [code], (err, result) => {
              if (err) throw err;
              res.send({
                error: 0,
                msg: "注册成功",
              });
            });
          });
        } else {
          res.send({
            error: 2,
            msg: "该用户名已被注册",
          });
        }
      });
    }
  });
});

//用户登录
server.get("/login", (req, res) => {
  let sql = "SELECT id,username FROM user WHERE username=? AND password=MD5(?)";
  pool.query(sql, [req.query.username, req.query.password], (err, result) => {
    if (err) throw err;
    if (result.length == 0) {
      res.send({
        code: 0,
        msg: "用户名或密码错误",
      });
    } else {
      res.send({
        code: 1,
        msg: "登录成功",
        id: result[0].id,
      });
    }
  });
});
//分类演出
server.get("/every", (req, res) => {
  let sql = "SELECT id FROM list WHERE idname=?";
  pool.query(sql, [req.query.cate], (err, result) => {
    if (err) throw err;
    let sql2 =
      "SELECT  id,verticalPic,name,price,address,date FROM allPerformances WHERE list_id=?";
    pool.query(sql2, [result[0].id], (err, result) => {
      if (err) throw err;
      res.send({
        result: result,
      });
    });
  });
});

//获取用户信息
server.get("/user", (req, res) => {
  let username = req.query.username;
  let sql = "SELECT pic FROM user WHERE username=?";
  pool.query(sql, [username], (err, result) => {
    if (err) throw err;
    res.send({
      result: result,
    });
  });
});
// 轮播图
server.get("/carousel", (req, res) => {
  let sql = "SELECT pic FROM banner";
  pool.query(sql, (err, result) => {
    if (err) throw err;
    res.send({
      result: result,
    });
  });
});
// 头像库
server.get("/userpic", (req, res) => {
  let sql = "SELECT images FROM headPortrait";
  pool.query(sql, (err, result) => {
    if (err) throw err;
    res.send({
      result: result,
    });
  });
});
//修改头像
server.post("/updatepic", (req, res) => {
  let sql = "update user set pic=? where username=?";
  pool.query(sql, [req.body.pic, req.body.username], (err, result) => {
    if (err) throw err;
    res.send({
      msg: "头像已修改",
    });
  });
});
// 用户票
server.get("/car", (req, res) => {
  let id = req.query.userid;
  let sql = "SELECT allPerformances_id FROM userTicket WHERE user_id=?";
  let sql2 =
    "SELECT id,verticalPic,name,price,address,date FROM allPerformances WHERE id=?";
  let some = [];
  pool.query(sql, [id], (err, result) => {
    if (err) throw err;
    for (let i = 0; i < result.length; i++) {
      let leng = result.length;
      let mid = result[i].allPerformances_id;
      pool.query(sql2, [mid], (err, result) => {
        if (err) throw err;
        some.push(result[0]);
        if (i == leng - 1) {
          res.send({
            result: some,
          });
        }
      });
    }
  });
});
// 购票（未购过票）
server.post("/buy", (req, res) => {
  let user_id = req.body.userid;
  let allPerformances_id = req.body.perid;
  let ticketStall_id = req.body.ticketid;
  let content = req.body.content;
  let price = req.body.price;
  let seat = req.body.seat;
  let sql = "INSERT INTO userTicket SET ?";
  pool.query(
    sql,
    [{ content, ticketStall_id, user_id, allPerformances_id, price, seat }],
    (err, result) => {
      if (err) throw err;
      res.send({
        msg: "购票成功",
        code: 1,
      });
    }
  );
});
// 购票（已购票）
server.post("/buy2", (req, res) => {
  let user_id = req.body.userid;
  let allPerformances_id = req.body.perid;
  let ticketStall_id = req.body.ticketid;
  let content = req.body.content;
  let sql =
    "UPDATE userTicket SET content=? WHERE user_id=? AND allPerformances_id=? AND ticketStall_id=?";
  pool.query(
    sql,
    [content, user_id, allPerformances_id, ticketStall_id],
    (err, result) => {
      if (err) throw err;
      res.send({
        msg: "购票成功",
        code: 1,
      });
    }
  );
});
// 用户关注
server.get("/follow", (req, res) => {
  let id = req.query.userid;
  let sql = "SELECT actor_id FROM follow WHERE user_id=?";
  let sql2 = "SELECT id,actorName,actorImg,fans FROM actor WHERE id=?";
  let some = [];
  pool.query(sql, [id], (err, result) => {
    if (err) throw err;
    if (result.length) {
      for (let i = 0; i < result.length; i++) {
        let leng = result.length;
        let mid = result[i].actor_id;
        pool.query(sql2, [mid], (err, result) => {
          if (err) throw err;
          some.push(result[0]);
          if (i == leng - 1) {
            res.send({
              result: some,
            });
          }
        });
      }
    } else {
      res.send({
        result: some,
      });
    }
  });
});
// 关注
server.post("/love", (req, res) => {
  let user_id = req.body.userid;
  let actor_id = req.body.actorid;
  let sql = "INSERT INTO follow SET ?";
  pool.query(sql, [{ user_id, actor_id }], (err, result) => {
    if (err) throw err;
    res.send({
      msg: "关注成功",
      code: 1,
    });
  });
});
// 取消关注
server.post("/dellove", (req, res) => {
  let user_id = req.body.user_id;
  let actor_id = req.body.actor_id;
  let sql = "DELETE FROM follow WHERE user_id=? AND actor_id=?";
  pool.query(sql, [user_id, actor_id], (err, result) => {
    if (err) throw err;
    res.send({
      msg: "关注已取消",
      code: 1,
    });
  });
});
// 关注与否
server.get("/isLove", (req, res) => {
  let userId = req.query.user;
  let actorId = req.query.actor;
  let sql =
    "SELECT id,actor_id,user_id FROM follow WHERE user_id=? AND actor_id=?";
  pool.query(sql, [userId, actorId], (err, result) => {
    if (err) throw err;
    if (result.length == 0) {
      res.send({
        code: 1,
        msg: "尚未关注",
      });
    } else {
      res.send({
        code: 0,
        msg: "已关注",
      });
    }
  });
});
// 搜索
server.get("/search", (req, res) => {
  let name = "%" + req.query.text + "%";
  let sql =
    "SELECT id,verticalPic,name,price,address,date FROM allPerformances WHERE name LIKE ? LIMIT 5";
  pool.query(sql, [name], (err, result) => {
    if (err) throw err;
    if (result.length == 0) {
      res.send({
        code: 0,
        msg: "查询无结果",
      });
    } else {
      res.send({
        code: 1,
        result: result,
      });
    }
  });
});
// 票档
server.get("/price", (req, res) => {
  let id = req.query.id;
  let sql =
    "SELECT id,seat,price,content FROM ticketStall WHERE allPerformances_id=?";
  pool.query(sql, [id], (err, result) => {
    if (err) throw err;
    res.send({
      result: result,
    });
  });
});
// 验证票的购买
server.get("/buycar", (req, res) => {
  let id = req.query.userid;
  let id2 = req.query.carid;
  let id3 = req.query.perid;
  let sql =
    "SELECT content FROM userTicket WHERE user_id=? AND ticketStall_id=? AND allPerformances_id=?";
  pool.query(sql, [id, id2, id3], (err, result) => {
    if (err) throw err;
    res.send({
      result: result,
    });
  });
});
// 去除余票1
server.get("/deltick", (req, res) => {
  let id = req.query.id;
  let sql = "SELECT content FROM ticketStall WHERE id=?";
  pool.query(sql, [id], (err, result) => {
    if (err) throw err;
    res.send({
      result: result,
    });
  });
});
// 去除余票2
server.post("/deltick", (req, res) => {
  let id = req.body.id;
  let num = req.body.num;
  let sql = "UPDATE ticketStall SET content=? WHERE id=?";
  pool.query(sql, [num, id], (err, result) => {
    if (err) throw err;
    res.send({
      msg: "ok",
    });
  });
});
// 自己的票详情
server.get("/ticket", (req, res) => {
  let id = req.query.userid;
  let id2 = req.query.perid;
  let sql =
    "SELECT ticketStall_id,content,price,seat FROM userTicket WHERE user_id=? AND allPerformances_id=?";
  pool.query(sql, [id, id2], (err, result) => {
    if (err) throw err;
    res.send({
      result: result,
    });
  });
});
// 退票
server.post("/return", (req, res) => {
  let userid = req.body.userid;
  let perid = req.body.perid;
  let ticketid = req.body.ticketid;
  let content = req.body.content;
  console.log(userid,perid,ticketid)
  let sql =
    "DELETE FROM userTicket WHERE user_id=? AND allPerformances_id=? AND ticketStall_id=?";
  let sql2 = "UPDATE ticketStall SET content=content+? WHERE id=?";
  pool.query(sql, [userid, perid, ticketid], (err, result) => {
    if (err) throw err;
    pool.query(sql2, [content, ticketid], (err, result) => {
      if (err) throw err;
      res.send({
        msg: "退票成功",
        code: 1,
      });
    });
  });
});
// 用户地址
server.get('/address',(req,res)=>{
    let id=req.query.userid;
    let sql="SELECT id,address FROM shippingAddress WHERE user_id=?";
    pool.query(sql,[id],(err,result)=>{
        if(err)throw err;
        res.send({
            result:result
        })
    })
})
// 添加地址
server.post('/newAdd',(req,res)=>{
    let id=req.body.userid;
    let newAdd=req.body.newAdd;
    let sql="INSERT INTO shippingAddress SET ?";
    pool.query(sql,[{user_id:id,address:newAdd}],(err,result)=>{
        if(err)throw err;
        res.send({
            code:1,
            msg:"ok"
        })
    })

})
// 删除地址
server.post('/delAdd',(req,res)=>{
    let id=req.body.id;
    let sql="DELETE FROM shippingAddress WHERE id=?"
    pool.query(sql,[id],(err,results)=>{
        if(err) throw err;
        res.send({
            code:1,
            msg:"地址已删除"
        })
    })
})

//指定监听端口
server.listen(3000, () => {
  console.log("server is running...");
});
